package com.example.demo01.Service.mobileService;

import com.example.demo01.Entity.webEntity.XYValueEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.Nullable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@RestController
public class CommonInfoService {

    @Autowired
    JdbcTemplate jdbcTemplate;

    /**
     * 通过用户名（手机号）获取报警次数
     * @param username
     * @return
     */
    @RequestMapping("/commoninfo/GET/warntimes")
    public String getWarnTimesByUserName(
            @RequestParam(value = "username") String username){
        String sql="SELECT COUNT(*) as warntimes \n" +
                "FROM warnrecord INNER JOIN clientuser ON warnrecord.idnumber=clientuser.idnum\n" +
                "WHERE phoneNum=?";
        return jdbcTemplate.queryForObject(sql,new Object[]{username},String.class);
    }

    /**
     * 通过用户名（手机号）获取上门服务次数
     * @param username
     * @return
     */
    @RequestMapping("/commoninfo/GET/servicetimes")
    public String getServiceTimesByUserName(
            @RequestParam(value = "username") String username){
        String sql="SELECT COUNT(*) as servicetimes\n" +
                "FROM serviceorders\n" +
                "WHERE clientusername=?";
        return jdbcTemplate.queryForObject(sql,new Object[]{username},String.class);
    }

    /**
     * 获取报警次数的统计数据
     */
    @RequestMapping("/commoninfo/GET/warntimes/statistics")
    public List<XYValueEntity> getWarnListByUserName(String username){
        String sql="SELECT wlon,wlat,COUNT(*) as value\n" +
                "FROM warnrecord INNER JOIN clientuser ON warnrecord.idnumber=clientuser.idnum\n" +
                "WHERE phoneNum=? GROUP BY wlon,wlat";
        return jdbcTemplate.query(sql, new Object[]{username}, new RowMapper<XYValueEntity>() {
            @Nullable
            @Override
            public XYValueEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                return new XYValueEntity(
                        resultSet.getFloat("wlon"),
                        resultSet.getFloat("wlat"),
                        resultSet.getFloat("value")
                );
            }
        });
    }


    /**
     * 获取服务次数信息的统计数据
     */
    @RequestMapping("/commoninfo/GET/servicetimes/statistics")
    public List<XYValueEntity> getServiceListByUserName(
            @RequestParam(value = "username")  String username){
        String sql="SELECT lon,lat,count(*) as value\n" +
                "FROM serviceorders\n" +
                "WHERE clientusername=?\n" +
                "GROUP BY lon,lat";
        return jdbcTemplate.query(sql, new Object[]{username}, new RowMapper<XYValueEntity>() {
            @Nullable
            @Override
            public XYValueEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                return new XYValueEntity(
                        resultSet.getFloat("lon"),
                        resultSet.getFloat("lat"),
                        resultSet.getFloat("value")
                );
            }
        });
    }

}
